const util = require("../../common/util");
const config = require("../../common/config");
const sqlite3 = require("sqlite3");

class GoodsDB {
  /**
   * 构造函数
   * @param {String} dbFile 数据库文件路径
   * @constructor
   */
  constructor() {
    this.dbFile = config.dbFile;
    this.instance = null;
    this.db = null;
  }
  // 获取数据库实例
  /**
   * @returns {GoodsDB} GoodsDB实例
   */
  static getInstance() {
    if (!this.instance) {
      try {
        this.instance = new GoodsDB();
      } catch (err) {
        util.err(err);
      }
    }
    util.log("GoodsDB实例已创建");
    return this.instance;
  }

  /**
   * @returns {Promise} Promise对象
   */
  async connect() {
    if (this.isConnected) return; // 避免重复连接
    return new Promise((resolve, reject) => {
      this.db = new sqlite3.Database(this.dbFile, (err) => {
        if (err) {
          util.err("数据库连接失败", err);
          return reject(err);
        } else {
          this.isConnected = true;
          util.log("数据库连接成功");
          resolve();
        }
      });

      // ✅ 连接错误监听（持续重连逻辑）
      this.db.on("error", (err) => {
        util.err("数据库连接断开", err);
        this.isConnected = false;
        // 可选：5秒后自动重连
        setTimeout(() => this.connect(), 5000);
      });
    });
  }

  findById(goodsId) {
  return new Promise((resolve, reject) => {
    const sql = `
      SELECT g.*, i.img_url AS first_img_url
      FROM goods g
      LEFT JOIN imgs i ON g.goods_id = i.goods_id
      WHERE g.goods_id = ?
      ORDER BY i.img_id ASC
      LIMIT 1
    `;
    this.db.get(sql, [goodsId], (err, result) => {
      if (err) return reject(err);

      if (!result) {
        return resolve(null); // 商品不存在
      }

      // 返回商品信息 + 第一张图片 URL
      const { first_img_url, ...goodsInfo } = result;
      resolve({
        ...goodsInfo,
        first_img_url: first_img_url || null, // 如果没有图片则返回 null
      });
    });
  });
  }

  add(goods) {
    return new Promise((resolve, reject) => {
      const sql = `
                INSERT INTO goods (
                    seller, goods_name, goods_desc, goods_price, goods_stock,
                    goods_detail, created_at, updated_at
                ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
            `;
      const values = [
        goods.seller,
        goods.goods_name,
        goods.goods_desc,
        goods.goods_price,
        goods.goods_stock,
        goods.goods_detail,
        new Date().toISOString(),
        new Date().toISOString(),
      ];

      this.db.run(sql, values, function (err) {
        if (err) {
          reject(err);
        } else {
          util.log(`Inserted goods with id: ${this.lastID}`);
          resolve(this.lastID); // 返回插入的goods_id
        }
      });
    });
  }

  addImg(goods) {
    return new Promise((resolve, reject) => {
      let sql = `INSERT INTO imgs (goods_id, img_url) VALUES (?, ?)`;
      let values = [goods.goods_id, goods.img_url];
      util.log(values);
      this.db.run(sql, values, function (err) {
        if (err) {
          reject(err);
        } else {
          resolve(this.lastID); // 返回插入的goods_id
        }
      });
    });
  }

  update(goods) {
    return new Promise((resolve, reject) => {
      const sql = `
                UPDATE goods SET
                    goods_name = ?, goods_desc = ?, goods_price = ?, goods_stock = ?,
                   goods_detail = ?,  updated_at = ?
                WHERE goods_id = ?
            `;
      const params = [
        goods.goods_name,
        goods.goods_desc,
        goods.goods_price,
        goods.goods_stock,
        goods.goods_detail,
        new Date().toISOString(),
        goods.goods_id,
      ];

      this.db.run(sql, params, function (err) {
        err ? reject(err) : resolve(this.changes);
      });
    });
  }

  // findByName(goods_name = "", limit = 10, offset = 0) {
  //   return new Promise((resolve, reject) => {
  //     // 强制转换为字符串类型
  //     const searchTerm = String(goods_name).trim();
  //     let sql = `
  //           SELECT g.*, GROUP_CONCAT(i.img_url) AS img_urls
  //           FROM goods g
  //           LEFT JOIN imgs i ON g.goods_id = i.goods_id
  //       `;
  //     const params = [];

  //     // 修改条件判断
  //     if (searchTerm) {
  //       sql += ` WHERE g.goods_name LIKE ?`;
  //       params.push(`%${searchTerm}%`);
  //     }

  //     sql += `
  //           GROUP BY g.goods_id
  //           ORDER BY g.goods_id DESC
  //           LIMIT ? OFFSET ?
  //       `;
  //     params.push(limit, offset);

  //     this.db.all(sql, params, function (err, result) {
  //       err ? reject(err) : resolve(result);
  //     });
  //   });
  // }

  // 在 GoodsDB 类中添加

  findByName(goods_name = "", limit = 10, offset = 0) {
    return new Promise((resolve, reject) => {
      const searchTerm = String(goods_name).trim();

      // 基础查询语句
      let dataSql = `
      SELECT g.*, GROUP_CONCAT(i.img_url) AS img_urls 
      FROM goods g
      LEFT JOIN imgs i ON g.goods_id = i.goods_id 
    `;

      // 总数查询语句
      let countSql = `SELECT COUNT(DISTINCT g.goods_id) as total FROM goods g `;

      const params = [];
      let whereClause = "WHERE g.goods_status = 1 AND g.goods_stock > 0";

      // 动态添加商品名称筛选条件
      if (searchTerm) {
        whereClause += " AND g.goods_name LIKE ?";
        params.push(`%${searchTerm}%`);
      }

      // 应用WHERE条件
      dataSql += whereClause;
      countSql += whereClause;

      // 分页查询语句
      dataSql += `
      GROUP BY g.goods_id
      ORDER BY g.goods_id DESC
      LIMIT ? OFFSET ?
    `;

      // 并行执行查询
      Promise.all([
        // 数据查询
        new Promise((res, rej) => {
          const dataParams = [...params, limit, offset];
          this.db.all(dataSql, dataParams, (err, result) => {
            err ? rej(err) : res(result);
          });
        }),
        // 总数查询
        new Promise((res, rej) => {
          this.db.get(countSql, params, (err, result) => {
            err ? rej(err) : res(result?.total || 0);
          });
        }),
      ])
        .then(([data, total]) => {
          resolve({ total, data });
        })
        .catch(reject);
    });
  }

  async getTotalCount() {
    return new Promise((resolve, reject) => {
      this.db.get("SELECT COUNT(*) as total FROM goods", (err, result) => {
        err ? reject(err) : resolve(result.total);
      });
    });
  }

  findByCategory(goods_category, limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
      let sql = `SELECT * FROM goods WHERE goods_category= ?
             order by goods_id desc`;
      let params = [goods_category];
      if (limit == -1) {
        sql += ` offset ?`;
        params.push(offset);
      } else if (offset == -1) {
        sql += ` limit ?`;
        params.push(limit);
      } else {
        sql += ` limit ? offset ?`;
        params.push(limit, offset);
      }

      this.db.all(sql, params, function (err, result) {
        err ? reject(err) : resolve(result);
      });
    });
  }

  // 在 GoodsDB 类中添加
  async findBySellerTotal(seller) {
    return new Promise((resolve, reject) => {
      this.db.all(
        `select count(*) as total from goods where seller = ?`,
        [seller],
        function (err, result) {
          err ? reject(err) : resolve(result[0].total);
        }
      );
    });
  }

  findBySeller(seller, limit = -1, offset = -1) {
    return new Promise((resolve, reject) => {
      let sql = `SELECT * FROM goods WHERE seller= ?
             order by goods_id desc`;
      let params = [seller];
      if (limit == -1) {
        sql += ` offset ?`;
        params.push(offset);
      } else if (offset == -1) {
        sql += ` limit ?`;
        params.push(limit);
      } else {
        sql += ` limit ? offset ?`;
        params.push(limit, offset);
      }

      this.db.all(sql, params, function (err, result) {
        err ? reject(err) : resolve(result);
      });
    });
  }

  findByPrice(min_price, max_price, limit = -1, offset = -1) {
    let sql = `SELECT * FROM goods WHERE goods_price BETWEEN ? AND ?`;
    const params = [min_price, max_price];
    if (limit == -1) {
      sql += " offset ?";
      params.push(offset);
    } else if (offset == -1) {
      sql += "limit ?";
      params.push(limit);
    } else {
      sql += "limit ? offset ?";
      params.push(limit, offset);
    }

    return new Promise((resolve, reject) => {
      this.db.all(sql, params, function (err, result) {
        err ? reject(err) : resolve(result);
        util.log(result);
      });
    });
  }

  addBrowseCount(goods_id) {
    const sql = `UPDATE goods SET browse_count = browse_count + 1 WHERE goods_id = ?`;
    return new Promise((resolve, reject) => {
      this.db.run(sql, [goods_id], function (err, result) {
        err ? reject(err) : resolve(result);
      });
    });
  }

  setStatus(goods_id, status) {
    const sql = `UPDATE goods SET goods_status = ? WHERE goods_id = ?`;
    return new Promise((resolve, reject) => {
      this.db.run(sql, [status, goods_id], function (err, result) {
        err ? reject(err) : resolve(result);
      });
    });
  }

  remove(goods_id) {
    const sql = `DELETE FROM goods WHERE goods_id = ?`;
    return new Promise((resolve, reject) => {
      this.db.run(sql, [goods_id], function (err, result) {
        err ? reject(err) : resolve(result);
        util.log(result);
      });
    });
  }
}

module.exports = GoodsDB;
